package org.southyin.dao;

import org.southyin.model.CmsArticle;
import org.southyin.model.CmsCategory;
import org.southyin.model.CmsCustomer;
import org.southyin.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CmsArticleDaoImpl implements CmsArticleDao {
    public int saveArticleInfo(CmsArticle cmsArticle) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into cms_article values(default,?,?,?,?,?,default,default,default,default,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,cmsArticle.getTitle());
            ps.setString(2,cmsArticle.getMdContent());
            ps.setString(3,cmsArticle.getHmContent());
            ps.setLong(4,cmsArticle.getUid());
            ps.setLong(5,cmsArticle.getCid());
            ps.setInt(6,cmsArticle.getStatus());
            count = ps.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    public List<CmsArticle> listHotOrNewOrHotList(String listId,int pageNum,int pageSize) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<CmsArticle> list = new ArrayList<CmsArticle>();
        CmsArticle article = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "";
            if (listId.equals("1")) {
                sql = "select a.id,title,publishTime,substr(hmContent,1,5) as descr,views,giveLike,u.id as authorId,nickname,c.id as caid,cname\n" +
                        "from cms_article a\n" +
                        "join cms_customer u\n" +
                        "on a.uid = u.id\n" +
                        "join cms_category c\n" +
                        "on a.cid = c.id\n" +
                        "where status = 1\n" +
                        "order by views desc,publishTime desc\n" +
                        "limit ?,?";
            }else if(listId.equals("2")){
                sql = "select a.id,title,publishTime,substr(hmContent,1,5) as descr,views,giveLike,u.id as authorId,nickname,c.id as caid,cname\n" +
                        "from cms_article a\n" +
                        "join cms_customer u\n" +
                        "on a.uid = u.id\n" +
                        "join cms_category c\n" +
                        "on a.cid = c.id\n" +
                        "where status = 1\n" +
                        "order by publishTime desc,title\n" +
                        "limit ?,?";
            }else{
                sql = "select a.id,title,publishTime,substr(hmContent,1,5) as descr,views,giveLike,u.id as authorId,nickname,c.id as caid,cname\n" +
                        "from cms_article a\n" +
                        "join cms_customer u\n" +
                        "on a.uid = u.id\n" +
                        "join cms_category c\n" +
                        "on a.cid = c.id\n" +
                        "where status = 1\n" +
                        "order by giveLike desc,publishTime desc\n" +
                        "limit ?,?";
            }
            ps = conn.prepareStatement(sql);
            ps.setInt(1,(pageNum-1)*pageSize);
            ps.setInt(2,pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                article = new CmsArticle();
                article.setId(rs.getLong("id"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getTimestamp("publishTime"));
                article.setHmContent(rs.getString("descr"));
                article.setViews(rs.getInt("views"));
                article.setGiveLike(rs.getInt("giveLike"));
                CmsCustomer author = new CmsCustomer();
                author.setId(rs.getLong("authorId"));
                author.setNickname(rs.getString("nickname"));
                article.setAuthor(author);
                CmsCategory category = new CmsCategory();
                category.setId(rs.getLong("caid"));
                category.setCname(rs.getString("cname"));
                article.setCategory(category);
                list.add(article);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    public int getTotalCounsOfArticles(Long cid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "";
            if (cid != null) {
                sql = "select count(id) as counts from cms_article where status = ? and cid = ?";
            }else{
                sql = "select count(id) as counts from cms_article where status = ?";
            }
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1);
            if (cid != null) {
                ps.setLong(2, cid);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                count = rs.getInt("counts");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    public CmsArticle getArticleById(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        CmsArticle article = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select a.id,title,hmContent,views,giveLike,publishTime,u.id as authorId,nickname\n" +
                    "from cms_article a\n" +
                    "join cms_customer u\n" +
                    "on u.id = a.uid\n" +
                    "where a.id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                article = new CmsArticle();
                article.setId(rs.getLong("id"));
                article.setTitle(rs.getString("title"));
                article.setHmContent(rs.getString("hmContent"));
                article.setViews(rs.getInt("views"));
                article.setGiveLike(rs.getInt("giveLike"));
                article.setPublishTime(rs.getTimestamp("publishTime"));
                CmsCustomer author = new CmsCustomer();
                author.setId(rs.getLong("authorId"));
                author.setNickname(rs.getString("nickname"));
                article.setAuthor(author);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return article;
    }

    public int updateArticleViews(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update cms_article set views = views + 1 where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1,id);
            count = ps.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }
}
